package com.kizuki.jdbc;

import java.sql.*;

public class TestJdbc {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");  // 必须完成类初始化，不然解析字符串的时候会报错

        try ( // try-with-resources
              Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8","root","admin");
              Statement s = c.createStatement(); // 方式1 需要进行字符串拼接，可读性和维护性比较差
              PreparedStatement psInsert = c.prepareStatement("insert into hero values(null, ?)"); // 方式2 使用参数设置，可读性好，不易犯错
              PreparedStatement psSelect = c.prepareStatement("select * from hero where name = ?")
        ){

            //------------------------------------------ 简单应用 ------------------------------------------
            //String sql = "insert into hero values()";
            //String sql = "delete from hero where id = 5";
            //String sql = "update hero set name = 'name 5' where id = 3";
            //s.execute(sql);

            //psSelect.setString(1, "第x位英雄"); // 从1开始, 将psSelect中的sql占位符用第二参数的代替
            //psSelect.execute();

            //------------------------------------------ Statement ------------------------------------------
            String sql = "select * from hero";
            ResultSet rs = s.executeQuery(sql);

            while(rs.next()) {
                int id = rs.getInt("id");// 可以使用字段名
                String name = rs.getString(2);// 也可以使用字段的顺序
                System.out.printf("%d\t%s\n", id, name);
            }

            // Statement执行10次，需要10次把SQL语句传输到数据库端
            // 数据库要对每一次来的SQL语句进行编译处理
            for (int i = 0; i < 10; i++) {
                String sql0 = "insert into hero values()";
                s.execute(sql0);
            }

            // 避免注入式攻击
            String name = "'盖伦' OR 1=1";
            String sql0 = "select * from hero where name = " + name;
            ResultSet resultSet1 = s.executeQuery(sql0);
            while (resultSet1.next()) { // 查出所有数据
                String heroName = resultSet1.getString("name");
                System.out.println(heroName);
            }

            //------------------------------------------ PreparedStatement ------------------------------------------
            // 每次执行，只需要传输参数到数据库端
            // 1. 网络传输量比Statement更小
            // 2. 数据库不需要再进行编译，响应更快
            for (int i = 0; i < 10; i++) {
                psInsert.setString(1, "提莫");
                psInsert.execute();
            }

            // 使用预编译Statement就可以杜绝SQL注入
            psSelect.setString(1, name);

            ResultSet resultSet2 = psSelect.executeQuery();
            // 查不出数据出来
            System.out.println("开始查询注入攻击测试");
            while (resultSet2.next()) {
                String heroName = resultSet2.getString("name");
                System.out.println(heroName);
            }
            System.out.println("执行结束");

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
